#region Copyright
/// <copyright>
/// Copyright (c) 2007 Ilia Shramko.
/// Released under the terms of the GNU General Public License.
/// </copyright>
#endregion

using System;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;

using Osh.Configuration;

namespace Osh.Data
{
	/// <summary>
	/// Summary description for DataContext.
	/// </summary>
	public class DataContext
	{
		private static string DefaultConnectionStringName = "OSH";
		private static string DefaultDatabaseName = "OSH.mdb";
		private static string DefaultDatabaseConnectionStringFormat = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}";

		private string _connectionString;
		private bool _sqlClient;
		private IDbCommand _selectEpgEventCommand;
		private IDbCommand _insertEpgEventCommand;
		private IDbCommand _updateEpgEventCommand;
		private IDbConnection _connection;

		static DataContext()
		{
			EnsureDefaultDatabaseExists();
		}

		public DataContext()
		{
			_connectionString = this.ConnectionString;
			_sqlClient =
				_connectionString.IndexOf("Microsoft.Jet.OLEDB", StringComparison.InvariantCultureIgnoreCase) == -1 &&
				_connectionString.IndexOf("Microsoft.SQLSERVER.MOBILE.OLEDB", StringComparison.InvariantCultureIgnoreCase) == -1;
		}

		public string ConnectionString
		{
			get
			{
				ConnectionStringSettings settings = Config.Current.GetConnectionStringSettings(DefaultConnectionStringName);
				string connectionString = null;
				if(settings != null)
					connectionString = settings.ConnectionString;
				if(string.IsNullOrEmpty(connectionString))
					connectionString = GetDefaultDatabaseConnectionString();
				return connectionString;
			}
		}

		private static string GetDefaultDatabasePath()
		{
			return Path.Combine(Config.Current.LocalApplicationDataFolder, DefaultDatabaseName);
		}

		private static string GetDefaultDatabaseConnectionString()
		{
			return string.Format(DefaultDatabaseConnectionStringFormat, GetDefaultDatabasePath());
		}

		private static void EnsureDefaultDatabaseExists()
		{
			ConnectionStringSettings settings = Config.Current.GetConnectionStringSettings(DefaultConnectionStringName);
			
			if(settings != null /*&& !string.Equals(settings.ProviderName, "System.Data.OleDb", StringComparison.InvariantCultureIgnoreCase)*/)
				return;

			string databasePath = GetDefaultDatabasePath();
			string connectionString = GetDefaultDatabaseConnectionString();
			if(!File.Exists(databasePath))
			{
				string databaseFolderPath = Path.GetDirectoryName(databasePath);
				if(!Directory.Exists(databaseFolderPath))
					Directory.CreateDirectory(databaseFolderPath);

				CreateDefaultDatabase(connectionString);
			}
			EnsureDefaultDatabaseStructure(connectionString);
		}

		private static void CreateDefaultDatabase(string connectionString)
		{
			object catalogObj = Activator.CreateInstance(Type.GetTypeFromProgID("ADOX.Catalog", true));
			catalogObj.GetType().InvokeMember("Create", System.Reflection.BindingFlags.InvokeMethod, null, catalogObj,
				new object[] { connectionString });
			System.Runtime.InteropServices.Marshal.ReleaseComObject(catalogObj);
		}

		private static void EnsureDefaultDatabaseStructure(string connectionString)
		{
			using(OleDbConnection connection = new OleDbConnection(connectionString))
			{
				connection.Open();
				DataTable table = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, "EitEvents", "TABLE" });
				if(table.Rows.Count == 0)
					CreateEitEventsTable(connection);
			}
		}

		private static void CreateEitEventsTable(OleDbConnection connection)
		{
			new OleDbCommand(
				"create table EitEvents (" +
					"Id autoincrement," +
					"NetworkId int," +
					"TransportId int," +
					"ServiceId int," +
					"EventId int," +
					"StartTime datetime," +
					"EndTime datetime," +
					"Duration int," +
					"[Language] char(3)," +
					"EventName nvarchar(255)," +
					"[Text] nvarchar(255)" +
				")",
				connection).ExecuteNonQuery();

			new OleDbCommand(
				"CREATE UNIQUE INDEX PK_EitEvents ON EitEvents " +
				"(NetworkId, TransportId, ServiceId, EventId, [Language]) " +
				"WITH PRIMARY",
				connection).ExecuteNonQuery();

			new OleDbCommand(
				"CREATE INDEX IX_EitEvents_NTSLSE ON EitEvents " +
				"(NetworkId, TransportId, ServiceId, [Language], StartTime)",
				connection).ExecuteNonQuery();
		}

		public void Close()
		{
			if(_connection != null)
			{
				_connection.Dispose();
				_connection = null;
			}
		}

		public IDbConnection Connection
		{
			get
			{
				if(_connection == null)
				{
					_connection = CreateConnection();
					_connection.Open();
				}
				return _connection;
			}
		}

		public IDataReader GetEpgEvents(int transportId, int networkId, int serviceId, 
			DateTime startTime, DateTime endTime)
		{
			if(_selectEpgEventCommand == null)
			{
				IDbCommand command = CreateCommand();
				command.CommandText = 
					"select * from EitEvents " + 
					"where " +
					"  NetworkId = @NetworkId and TransportId = @TransportId and ServiceId = @ServiceId and " + 
					"  EndTime > @StartTime and StartTime < @EndTime " +
					"order by " + 
					"  [Language], StartTime";

				command.CommandType = CommandType.Text;
				AddParameter(command, "@NetworkId", DbType.Int32);       // 0 
				AddParameter(command, "@TransportId", DbType.Int32);     // 1
				AddParameter(command, "@ServiceId", DbType.Int32);       // 2
				AddParameter(command, "@StartTime", DbType.DateTime);          // 3
				AddParameter(command, "@EndTime", DbType.DateTime);            // 4
				_selectEpgEventCommand = command;
			}

			_selectEpgEventCommand.Connection = this.Connection;
			SetParameter(_selectEpgEventCommand, 0, networkId);
			SetParameter(_selectEpgEventCommand, 1, transportId);
			SetParameter(_selectEpgEventCommand, 2, serviceId);
			SetParameter(_selectEpgEventCommand, 3, startTime);
			SetParameter(_selectEpgEventCommand, 4, endTime);
			return _selectEpgEventCommand.ExecuteReader();
		}

		public int SaveEpgEvent(int transportId, int networkId, int serviceId, int eventId,
			DateTime startTime, int duration, string language, string eventName, string text)
		{
			int rows = UpdateEpgEvent(transportId, networkId, serviceId, eventId,
				startTime, duration, language, eventName, text);
			if(rows > 0)
				return rows;

			return InsertEpgEvent(transportId, networkId, serviceId, eventId,
					startTime, duration, language, eventName, text);
		}

		private int InsertEpgEvent(int transportId, int networkId, int serviceId, int eventId,
			DateTime startTime, int duration, string language, string eventName, string text)
		{
			if(_insertEpgEventCommand == null)
			{
				IDbCommand command = CreateCommand();
				command.CommandText = 
					"insert into EitEvents " + 
					"(NetworkId, TransportId, ServiceId, EventId, StartTime, EndTime, Duration, [Language], EventName, [Text]) " + 
					"values " +
					"(@NetworkId, @TransportId, @ServiceId, @EventId, @StartTime, @EndTime, @Duration, @Language, @EventName, @Text)";
				command.CommandType = CommandType.Text;
				AddParameter(command, "@NetworkId", DbType.Int32);       // 0 
				AddParameter(command, "@TransportId", DbType.Int32);     // 1
				AddParameter(command, "@ServiceId", DbType.Int32);       // 2
				AddParameter(command, "@EventId", DbType.Int32);         // 3
				AddParameter(command, "@StartTime", DbType.DateTime);          // 4
				AddParameter(command, "@EndTime", DbType.DateTime);            // 5
				AddParameter(command, "@Duration", DbType.Int32);        // 6
				AddParameter(command, "@Language", DbType.StringFixedLength, 3);       // 7
				AddParameter(command, "@EventName", DbType.String, 255); // 8
				AddParameter(command, "@Text", DbType.String, 255);      // 9
				_insertEpgEventCommand = command;
			}
			using(IDbConnection connection = CreateConnection())
			{
				connection.Open();
				_insertEpgEventCommand.Connection = connection;
				SetParameter(_insertEpgEventCommand, 0, networkId);
				SetParameter(_insertEpgEventCommand, 1, transportId);
				SetParameter(_insertEpgEventCommand, 2, serviceId);
				SetParameter(_insertEpgEventCommand, 3, eventId);
				SetParameter(_insertEpgEventCommand, 4, startTime);
				SetParameter(_insertEpgEventCommand, 5, startTime.AddSeconds(duration));
				SetParameter(_insertEpgEventCommand, 6, duration);
				SetParameter(_insertEpgEventCommand, 7, language);
				SetParameter(_insertEpgEventCommand, 8, eventName);
				SetParameter(_insertEpgEventCommand, 9, text);
				return _insertEpgEventCommand.ExecuteNonQuery();
			}
		}

		private int UpdateEpgEvent(int transportId, int networkId, int serviceId, int eventId,
			DateTime startTime, int duration, string language, string eventName, string text)
		{
			if(_updateEpgEventCommand == null)
			{
				IDbCommand command = CreateCommand();
				command.CommandText = 
					"update EitEvents set " + 
					"  StartTime = @StartTime, EndTime = @EndTime, Duration = @Duration, " + 
					"  EventName = @EventName, [Text] = @Text " + 
					"where " +
					"  NetworkId = @NetworkId and TransportId = @TransportId and " +
					"  ServiceId = @ServiceId and EventId = @EventId and " + 
					"  [Language] = @Language";

				command.CommandType = CommandType.Text;
				AddParameter(command, "@StartTime", DbType.DateTime);          // 0
				AddParameter(command, "@EndTime", DbType.DateTime);            // 1
				AddParameter(command, "@Duration", DbType.Int32);        // 2
				AddParameter(command, "@EventName", DbType.String, 255); // 3
				AddParameter(command, "@Text", DbType.String, 255);      // 4
				AddParameter(command, "@NetworkId", DbType.Int32);       // 5 
				AddParameter(command, "@TransportId", DbType.Int32);     // 6
				AddParameter(command, "@ServiceId", DbType.Int32);       // 7
				AddParameter(command, "@EventId", DbType.Int32);         // 8
				AddParameter(command, "@Language", DbType.StringFixedLength, 3);       // 9
				_updateEpgEventCommand = command;
			}
			using(IDbConnection connection = CreateConnection())
			{
				connection.Open();
				_updateEpgEventCommand.Connection = connection;
				SetParameter(_updateEpgEventCommand, 0, startTime);
				SetParameter(_updateEpgEventCommand, 1, startTime.AddSeconds(duration));
				SetParameter(_updateEpgEventCommand, 2, duration);
				SetParameter(_updateEpgEventCommand, 3, eventName);
				SetParameter(_updateEpgEventCommand, 4, text);
				SetParameter(_updateEpgEventCommand, 5, networkId);
				SetParameter(_updateEpgEventCommand, 6, transportId);
				SetParameter(_updateEpgEventCommand, 7, serviceId);
				SetParameter(_updateEpgEventCommand, 8, eventId);
				SetParameter(_updateEpgEventCommand, 9, language);
				return _updateEpgEventCommand.ExecuteNonQuery();
			}
		}

		private IDbConnection CreateConnection()
		{
			if(_sqlClient)
				return new SqlConnection(_connectionString);
			else
				return new OleDbConnection(_connectionString);
		}

		private IDbCommand CreateCommand()
		{
			if(_sqlClient)
				return new SqlCommand();
			else
				return new OleDbCommand();
		}

		private IDataParameter CreateParameter()
		{
			if(_sqlClient)
				return new SqlParameter();
			else
				return new OleDbParameter();
		}

		private IDataParameter AddParameter(IDbCommand command, string parameterName, DbType parameterType)
		{
			IDataParameter param = CreateParameter();
			param.ParameterName = parameterName;
			if((parameterType == DbType.Date || parameterType == DbType.DateTime) && !_sqlClient)
				((OleDbParameter)param).OleDbType = OleDbType.Date;
			else
				param.DbType = parameterType;
			command.Parameters.Add(param);
			return param;
		}

		private IDataParameter AddParameter(
			IDbCommand command, string parameterName, DbType parameterType, int size)
		{
			IDataParameter param = AddParameter(command, parameterName, parameterType);
			if(_sqlClient)
				((SqlParameter)param).Size = size;
			else
				((OleDbParameter)param).Size = size;
			return param;
		}

		private void SetParameter(IDbCommand command, int index, object value)
		{
			(command.Parameters[index] as IDataParameter).Value = value;
		}

	}
}
